Array Fields
Array fields provide a mechanism for efficiently storing and analyzing data where each field contains multiple answers. For example, a survey might ask the question, "Which Supermarkets do you shop at?". Possible answers could be none, one, some or all of the following:-
| 01 - Aldi | 05 - Kwik Save | 09 - Tesco |
| 02 - ASDA | 06 - Morrisons | 10 - Waitrose |
| 03 - Co-op | 07 - Sainsbury's | |
| 04 - Iceland | 08 - Somerfield |
The answers from each respondent would be encoded where Aldi = 01, ASDA = 02, Co-op = 03 etc.). Therefore, a person who shops at Tesco and Sainsbury's would have the value '0907, whereas someone who only shopped at Somerfield would have '08'.
This data is then stored in Engine as an array Field, where it can be analysed with queries, for example, "Give me all people who shop at ASDA, but never at Waitrose".
What is an Array Field?
An array field is a TEXT_FIELD and can be defined as being either FIXED or VARIABLE width. The raw data will consist of a string of codes that need to be decoded. In many ways the new array field can be treated just like any other field, but in a record-set the field displays the encoded data, not the decoded data.
After the data is loaded into Engine, it is displayed in MyData. This means that, for example, the Supermarket data shown above will be displayed as if the contents of the field were a list of supermarkets, i.e. decoded, but displayed in a record-set as the encoded data. This array field can then be cross tabulated, the labels would then be displayed as supermarket names. Queries can then be written referencing the supermarkets using the array field.
An array field is defined in iLoader as a TEXT field with format of either VARRAY|X or FARRAY|X (fixed or variable - see Data type limits and constraints in the Database Language Reference Library for more information), where X is the width of each item in the raw data (e.g. 2 in the example above) and an optional associated decode file. If you do not replace the X in the format field, the table load will be aborted.
Array fields can also specify a decode that translates the numerical value supplied in the data to descriptive names when using Crosstabs etc. (see Using the Table Definition Wizard for more information).
The following screen shot shows how this is represented in the Table Definition Wizard.
